﻿/****** Object:  StoredProcedure dbo.OWU_MerchantManagerSearch_Select    Script Date: 04/20/2010 12:17:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OWU_MerchantManagerSearch_Select') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.OWU_MerchantManagerSearch_Select
GO
/****** Object:  StoredProcedure dbo.OWU_MerchantManagerSearch_Select    Script Date: 04/20/2010 12:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dale Hildebrand
-- Create date: 04/20/2010
-- Description:	Select a list of all Merchant
-- Users matching on Uswername, FirstName,
-- LastName, DisplayName or Email
-- =============================================
CREATE PROCEDURE dbo.OWU_MerchantManagerSearch_Select
(
	 @SearchName NVARCHAR(50)
	,@PageNumber INT
	,@PageSize INT
)
AS
BEGIN
	SET NOCOUNT ON;

DECLARE @RoleName NVARCHAR(50)
SET @RoleName = 'Merchant'

DECLARE @UsersWithRowNumber TABLE(
		RowNumber INT,
		UserID INT,
		LegalCorporateName VARCHAR(50),
		BusinessName VARCHAR(50),
		ContactPerson VARCHAR(60),
		ContactTitle VARCHAR(50),
		BusinessPhone NVARCHAR(25),
		WebSite NVARCHAR(255),
		Expense1 VARCHAR(50),
		Expense2 VARCHAR(50),
		Expense3 VARCHAR(50),
		Expense4 VARCHAR(50),
		Expense5 VARCHAR(50)
	)
	INSERT INTO @UsersWithRowNumber
		SELECT ROW_NUMBER() OVER(ORDER BY U.UserID) AS RowNumber,
			U.UserID,
			Mer.LegalCorporateName,
			Mer.BusinessName,
			Mer.ContactPerson,
			Mer.ContactTitle,
			Mer.BusinessPhone,
			Mer.WebSite,
			Mer.Expense1,
			Mer.Expense2,
			Mer.Expense3,
			Mer.Expense4,
			Mer.Expense5
		FROM dbo.Users U
			INNER JOIN dbo.UserRoles UR
				ON U.UserID=UR.UserID
				AND NOT(U.IsDeleted=1)
			INNER JOIN [dbo].[Roles] R
				ON UR.RoleID=R.RoleID
				AND R.RoleName = @RoleName
			INNER JOIN dbo.Members Mem
				ON U.UserID=Mem.UserID
			INNER JOIN dbo.Merchants Mer
				ON Mem.MemberID=Mer.MemberID
				AND (
						Mer.LegalCorporateName LIKE '%'+@SearchName+'%'
						OR Mer.BusinessName LIKE '%'+@SearchName+'%'
					)

	SELECT
		 @PageNumber As PageNumber
		,CAST(CEILING(MAX(RowNumber)/CAST(@PageSize AS FLOAT)) AS INT) As NumberOfPages
	FROM @UsersWithRowNumber

	SELECT 
		UserID,
		LegalCorporateName,
		BusinessName,
		ContactPerson,
		ContactTitle,
		BusinessPhone,
		WebSite,
		Expense1,
		Expense2,
		Expense3,
		Expense4,
		Expense5
	FROM @UsersWithRowNumber
	WHERE RowNumber BETWEEN (@PageNumber - 1) * @PageSize + 1 
		AND @PageNumber * @PageSize
	ORDER BY LegalCorporateName,BusinessName;
END
GO


/****** Object:  StoredProcedure dbo.OWU_MerchantCardOrderForm_CardOrder_Insert    Script Date: 04/22/2010 08:58:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OWU_MerchantCardOrderForm_CardOrder_Insert') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.OWU_MerchantCardOrderForm_CardOrder_Insert
GO
/****** Object:  StoredProcedure dbo.OWU_MerchantCardOrderForm_CardOrder_Insert    Script Date: 04/22/2010 08:58:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dale Hildebrand
-- Create date: 04/21/2010
-- Description:	Order a card for the provided 
--				merchant
-- =============================================
CREATE PROCEDURE dbo.OWU_MerchantCardOrderForm_CardOrder_Insert
(
	@CreatedByUserID INT,
	@UserID INT,
	@FirstName NVARCHAR(30),
	@LastName NVARCHAR(30),
	@Address NVARCHAR(70),
	@City NVARCHAR(50),
	@StateProv NVARCHAR(40),
	@Country NVARCHAR(30),
	@ZipPostal NVARCHAR(10)
)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [dbo].[CardOrders]
           (UserID
           ,FirstName
           ,LastName
           ,Address
           ,City
           ,StateProv
           ,Country
           ,ZipPostal
           ,ValidFrom
           ,Expiry
           ,IsPrinted)
     VALUES
           (@UserID
           ,@FirstName
           ,@LastName
           ,@Address
           ,@City
           ,@StateProv
           ,@Country
           ,@ZipPostal
           ,GETDATE()
           ,NULL
           ,'no')
           
    DECLARE @CardOrderedRoleID INT
    SET @CardOrderedRoleID = (
		SELECT TOP(1) RoleID
		FROM Roles
		WHERE RoleName = 'Card Ordered'
	)
	
	IF NOT EXISTS(    
		SELECT UserID
		FROM UserRoles
		WHERE UserID=@UserID
			AND RoleID=@CardOrderedRoleID
	)
	BEGIN
		INSERT INTO dbo.UserRoles
				   (UserID
				   ,RoleID
				   ,ExpiryDate
				   ,IsTrialUsed
				   ,EffectiveDate
				   ,CreatedByUserID
				   ,CreatedOnDate
				   ,LastModifiedByUserID
				   ,LastModifiedOnDate)
			 VALUES
				   (@UserID
				   ,@CardOrderedRoleID
				   ,NULL
				   ,1
				   ,NULL
				   ,@CreatedByUserID
				   ,GETDATE()
				   ,@CardOrderedRoleID
				   ,GETDATE())
	END
END
GO


/****** Object:  StoredProcedure dbo.OWU_MerchantCardActivation_InsertRoles    Script Date: 04/20/2010 12:17:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OWU_MerchantCardActivation_InsertRoles') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OWU_MerchantCardActivation_InsertRoles]
GO
/****** Object:  StoredProcedure [dbo].[OWU_MerchantCardActivation_InsertRoles]    Script Date: 04/20/2010 12:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dale Hildebrand
-- Create date: 04/20/2010
-- Description:	Insert the Card Activatd Role
-- =============================================
CREATE PROCEDURE [dbo].[OWU_MerchantCardActivation_InsertRoles]
(
	 @CreatedByUserID INT
	,@UserID INT
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @RoleID INT
    SET @RoleID = (
		SELECT TOP(1) RoleID
		FROM Roles
		WHERE RoleName = 'Card Activated'
	)
	
	IF NOT EXISTS(    
		SELECT UserID
		FROM UserRoles
		WHERE UserID=@UserID
			AND RoleID=@RoleID
	)
	BEGIN
		INSERT INTO dbo.UserRoles
				   (UserID
				   ,RoleID
				   ,ExpiryDate
				   ,IsTrialUsed
				   ,EffectiveDate
				   ,CreatedByUserID
				   ,CreatedOnDate
				   ,LastModifiedByUserID
				   ,LastModifiedOnDate)
			 VALUES
				   (@UserID
				   ,@RoleID
				   ,NULL
				   ,1
				   ,NULL
				   ,@CreatedByUserID
				   ,GETDATE()
				   ,@RoleID
				   ,GETDATE())
	END
	
	SET @RoleID = (
		SELECT TOP(1) RoleID
		FROM Roles
		WHERE RoleName = 'Moneybar User'
	)
	IF NOT EXISTS(    
		SELECT UserID
		FROM UserRoles
		WHERE UserID=@UserID
			AND RoleID=@RoleID
	)
	BEGIN
		INSERT INTO dbo.UserRoles
				   (UserID
				   ,RoleID
				   ,ExpiryDate
				   ,IsTrialUsed
				   ,EffectiveDate
				   ,CreatedByUserID
				   ,CreatedOnDate
				   ,LastModifiedByUserID
				   ,LastModifiedOnDate)
			 VALUES
				   (@UserID
				   ,@RoleID
				   ,NULL
				   ,1
				   ,NULL
				   ,@CreatedByUserID
				   ,GETDATE()
				   ,@RoleID
				   ,GETDATE())
	END
END
GO
